﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;

namespace SD.DAL
{
    public class OrgDetail2DAO : BaseDAO, IOrgDetail2DAO
    {
        #region IOrgDetail2DAO Members

        /// <summary>
        /// Get all detail 2 of an organization
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <returns>Table contains detail 2 ID of an organization</returns>
        public List<String> GetByOrganization(string orgId)
        {
            SqlCommand cmd = CreateCommand();
            cmd.CommandText = "OrgDetail2GetByOrg";
            cmd.Parameters.AddWithValue("@orgId", orgId);

            List<String> lstResult = new List<string>();
            try
            {
                DataTable dtb = ExecuteSelectCommand(cmd);
                // ref id to result list
                foreach (DataRow row in dtb.Rows)
                {
                    lstResult.Add(row["RefID"].ToString());
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return lstResult;
        }

        /// <summary>
        /// Insert new detail 2
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <param name="refId">ID of the reference data</param>
        /// <returns>Indicate whether inserting succeeded</returns>
        public bool Insert(string orgId, string refId)
        {
            try
            {
                SqlCommand cmd = CreateCommand();
                cmd.CommandText = "OrgDetail2Insert";
                cmd.Parameters.AddWithValue("@orgId", orgId);
                cmd.Parameters.AddWithValue("@refId", refId);

                ExecuteNonQuery(cmd);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return true;
        }

        /// <summary>
        /// Update detail 2 of an organization
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <param name="newDetails">List of new details</param>
        /// <returns>Indicate whether updating succeeded</returns>
        public bool Update(string orgId, List<SD.Entities.ReferenceData> newDetails)
        {
            try
            {
                List<String> lstRefID = GetByOrganization(orgId);

                #region update details 2
                // remove deleted details
                foreach (String strRefID in lstRefID)
                {
                    Boolean isDeleted = true;
                    // search in new details list
                    foreach (ReferenceData refData in newDetails)
                    {
                        if (strRefID.Equals(refData.RefId))
                        {
                            isDeleted = false;
                            break;
                        }
                    }

                    if (isDeleted)
                    {
                        Delete(orgId, strRefID);
                    }
                }

                // insert new details
                foreach (ReferenceData refData in newDetails)
                {
                    Boolean isInserted = true;
                    // search in old details
                    foreach (String strRefID in lstRefID)
                    {
                        // Details is existing
                        if (strRefID.Equals(refData.RefId))
                        {
                            isInserted = false;
                            break;
                        }
                    }
                    // Not exist in database
                    if (isInserted)
                    {
                        Insert(orgId, refData.RefId);
                    }
                }
                #endregion
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return true;
        }

        /// <summary>
        /// Delete detail 2 of an organization
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <param name="refId">ID of the reference data</param>
        /// <returns>Indicate whether deleting succeeded</returns>
        public bool Delete(string orgId, string refId)
        {
            try
            {
                SqlCommand cmd = CreateCommand();
                cmd.CommandText = "OrgDetail2Delete";
                cmd.Parameters.AddWithValue("@orgId", orgId);
                cmd.Parameters.AddWithValue("@refId", refId);

                ExecuteNonQuery(cmd);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return true;
        }

        #endregion
    }
}
